﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class HisPositionDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public HisPositionDAL()
        {
        }

        public DataSet LoadHisPositions(Nullable<int> Sequence, Nullable<int> Employee)
        {
            string strSQL;
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@Employee", Employee);
                dbManager.AddParameters(2, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT h.*, m.cmd_type_name"
                    + " FROM HRM_PS_EMP_POSITION_HISTORIES h"
                    + " LEFT JOIN hrm_ps_command_types m ON h.cmd_type_seq = m.cmd_type_seq"
                    + " WHERE h.RECORD_STATUS = @ActiveRecord"
                    + (Sequence.HasValue ? " AND h.POS_HIS_SEQ = @Sequence" : "")
                    + (Employee.HasValue ? " AND h.EMP_SEQ = @Employee" : "")
                    + " ORDER BY h.START_DATE DESC, h.CMD_DATE DESC";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return ds;
        }
        public void Insert(

			string cmd_name,
			string cmd_ref_no,
			Nullable<DateTime> start_date,
			Nullable<DateTime> end_date,
			Nullable<DateTime> cmd_date,

            Nullable<int> emp_seq
            , Nullable<int> cmd_dtls_seq
            , Nullable<int> his_type

            , string pos_ref
            , string pos_name
            , string class_name
            , string job_site_name
            , string working_job_site_name
            , string department_name
            , string ministry_name
            , string field_name
            , string exec_name
            , Nullable<double> salary

            , string remarks
            , Nullable<int> CreatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(22);

                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(2, "@his_type", his_type);

                dbManager.AddParameters(3, "@pos_ref", pos_ref);
                dbManager.AddParameters(4, "@pos_name", pos_name);
                dbManager.AddParameters(5, "@class_name", class_name);
                dbManager.AddParameters(6, "@job_site_name", job_site_name);
                dbManager.AddParameters(7, "@working_job_site_name", working_job_site_name);
                dbManager.AddParameters(8, "@department_name", department_name);
                dbManager.AddParameters(9, "@ministry_name", ministry_name);
                dbManager.AddParameters(10, "@field_name", field_name);
                dbManager.AddParameters(11, "@exec_name", exec_name);
                dbManager.AddParameters(12, "@salary", salary);

                dbManager.AddParameters(13, "@remarks", remarks);
                dbManager.AddParameters(14, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(15, "@created_by", CreatedBy);
                dbManager.AddParameters(16, "@created_when", DateTime.Today);
				
                dbManager.AddParameters(17, "@cmd_name", cmd_name);
                dbManager.AddParameters(18, "@cmd_ref_no", cmd_ref_no);
                dbManager.AddParameters(19, "@start_date", start_date);
                dbManager.AddParameters(20, "@end_date", end_date);
                dbManager.AddParameters(21, "@cmd_date", cmd_date);

				strSQL = "INSERT INTO HRM_PS_EMP_POSITION_HISTORIES"
                    + "(  emp_seq, cmd_dtls_seq, his_type "
                    + ", pos_ref, pos_name"
					+ ", cmd_name, cmd_ref_no, start_date, end_date, cmd_date"
                    + ", class_name, job_site_name, working_job_site_name"
                    + ", department_name, ministry_name, field_name, exec_name, salary"
                    + ", remarks, record_status, created_by, created_when)"
					+ " VALUES (@emp_seq, @cmd_dtls_seq, @his_type "
					+ ", @pos_ref, @pos_name"
					+ ", @cmd_name, @cmd_ref_no, @start_date, @end_date, @cmd_date"
                    + ", @class_name, @job_site_name, @working_job_site_name"
                    + ", @department_name, @ministry_name, @field_name, @exec_name, @salary"
                    + ", @remarks, @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public void Insert(
            Nullable<int> emp_seq
            , Nullable<DateTime> start_date
            , Nullable<DateTime> end_date
            , Nullable<int> cmd_dtls_seq
            , string cmd_ref_no
            , Nullable<DateTime> cmd_date
            , string cmd_name

            , string old_pos_ref
            , string old_pos_name
            , string old_class_name
            , string old_job_site_name
            , string old_working_job_site_name
            , string old_dep_name
            , string old_mis_name
            , string old_field_name
            , string old_exec_name
            , Nullable<double> old_salary

            , string new_pos_ref
            , string new_pos_name
            , string new_class_name
            , string new_job_site_name
            , string new_working_job_site_name
            , string new_dep_name
            , string new_mis_name
            , string new_field_name
            , string new_exec_name
            , Nullable<double> new_salary

            , Nullable<int> cmd_type_seq
            , string remarks
            , Nullable<int> CreatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(32);

                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@start_date", start_date);
                dbManager.AddParameters(2, "@end_date", end_date);
                dbManager.AddParameters(3, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(4, "@cmd_ref_no", cmd_ref_no);
                dbManager.AddParameters(5, "@cmd_name", cmd_name);
                dbManager.AddParameters(6, "@cmd_date", cmd_date);

                dbManager.AddParameters(7, "@old_pos_ref", old_pos_ref);
                dbManager.AddParameters(8, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(9, "@old_class_name", old_class_name);
                dbManager.AddParameters(10, "@old_job_site_name", old_job_site_name);
                dbManager.AddParameters(11, "@old_working_job_site_name", old_working_job_site_name);
                dbManager.AddParameters(12, "@old_dep_name", old_dep_name);
                dbManager.AddParameters(13, "@old_mis_name", old_mis_name);
                dbManager.AddParameters(14, "@old_field_name", old_field_name);
                dbManager.AddParameters(15, "@old_exec_name", old_exec_name);
                dbManager.AddParameters(16, "@old_salary", old_salary);

                dbManager.AddParameters(17, "@new_pos_ref", new_pos_ref);
                dbManager.AddParameters(18, "@new_pos_name", new_pos_name);
                dbManager.AddParameters(19, "@new_class_name", new_class_name);
                dbManager.AddParameters(20, "@new_job_site_name", new_job_site_name);
                dbManager.AddParameters(21, "@new_working_job_site_name", new_working_job_site_name);
                dbManager.AddParameters(22, "@new_dep_name", new_dep_name);
                dbManager.AddParameters(23, "@new_mis_name", new_mis_name);
                dbManager.AddParameters(24, "@new_field_name", new_field_name);
                dbManager.AddParameters(25, "@new_exec_name", new_exec_name);
                dbManager.AddParameters(26, "@new_salary", new_salary);

                dbManager.AddParameters(27, "@cmd_type_seq", cmd_type_seq);
                dbManager.AddParameters(28, "@remarks", remarks);
                dbManager.AddParameters(29, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(30, "@created_by", CreatedBy);
                dbManager.AddParameters(31, "@created_when", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_EMP_POSITION_HISTORIES"
                    + "(emp_seq, start_date, end_date, cmd_dtls_seq"
                    + ", cmd_ref_no, cmd_date, cmd_name, old_pos_ref, old_pos_name"
                    + ", old_class_name, old_job_site_name, old_working_job_site_name"
                    + ", old_dep_name, old_mis_name, old_field_name, old_exec_name, old_salary"
                    + ", new_pos_ref, new_pos_name, new_class_name, new_job_site_name"
                    + ", new_working_job_site_name, new_dep_name, new_mis_name, new_field_name"
                    + ", new_exec_name, new_salary, cmd_type_seq, remarks, record_status, created_by, created_when)"
                    + " VALUES (@emp_seq, @start_date, @end_date, @cmd_dtls_seq"
                    + ", @cmd_ref_no, @cmd_date, @cmd_name, @old_pos_ref, @old_pos_name"
                    + ", @old_class_name, @old_job_site_name, @old_working_job_site_name"
                    + ", @old_dep_name, @old_mis_name, @old_field_name, @old_exec_name, @old_salary"
                    + ", @new_pos_ref, @new_pos_name, @new_class_name, @new_job_site_name"
                    + ", @new_working_job_site_name, @new_dep_name, @new_mis_name, @new_field_name"
                    + ", @new_exec_name, @new_salary, @cmd_type_seq, @remarks, @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Update(

			string cmd_name,
			string cmd_ref_no,
			Nullable<DateTime> start_date,
			Nullable<DateTime> end_date,
			Nullable<DateTime> cmd_date,

			int Sequence
            , Nullable<int> cmd_dtls_seq
            , Nullable<int> his_type

            , string pos_ref
            , string pos_name
            , string class_name
            , string job_site_name
            , string working_job_site_name
            , string department_name
            , string ministry_name
            , string field_name
            , string exec_name
            , Nullable<double> salary

            , string remarks
            , int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(22);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(2, "@his_type", his_type);

                dbManager.AddParameters(3, "@pos_ref", pos_ref);
                dbManager.AddParameters(4, "@pos_name", pos_name);
                dbManager.AddParameters(5, "@class_name", class_name);

                dbManager.AddParameters(6, "@job_site_name", job_site_name);
                dbManager.AddParameters(7, "@working_job_site_name", working_job_site_name);
                dbManager.AddParameters(8, "@department_name", department_name);

                dbManager.AddParameters(9, "@ministry_name", ministry_name);
                dbManager.AddParameters(10, "@field_name", field_name);
                dbManager.AddParameters(11, "@exec_name", exec_name);
                dbManager.AddParameters(12, "@salary", salary);

                dbManager.AddParameters(13, "@remarks", remarks);
                dbManager.AddParameters(14, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(15, "@updated_by", UpdatedBy);
				dbManager.AddParameters(16, "@updated_when", DateTime.Today);

				dbManager.AddParameters(17, "@cmd_name", cmd_name);
				dbManager.AddParameters(18, "@cmd_ref_no", cmd_ref_no);
				dbManager.AddParameters(19, "@start_date", start_date);
				dbManager.AddParameters(20, "@end_date", end_date);
				dbManager.AddParameters(21, "@cmd_date", cmd_date); 

                strSQL = "UPDATE HRM_PS_EMP_POSITION_HISTORIES"
                    + " SET CMD_DTLS_SEQ = @cmd_dtls_seq"
                        + ", HIS_TYPE = @his_type"

                        + ", pos_ref = @pos_ref"
                        + ", pos_name = @pos_name"
                        + ", class_name = @class_name"
                        + ", job_site_name = @job_site_name"
                        + ", working_job_site_name = @working_job_site_name"
						+ ", department_name = @department_name"
						+ ", ministry_name = @ministry_name"
                        + ", field_name = @field_name"
                        + ", exec_name = @exec_name"
                        + ", salary = @salary"

						+ ", cmd_name = @cmd_name"
						+ ", cmd_ref_no = @cmd_ref_no"
						+ ", start_date = @start_date"
						+ ", end_date = @end_date"
						+ ", cmd_date = @cmd_date" 

                        + ", REMARKS = @remarks"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE POS_HIS_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public void Update(int Sequence
            , Nullable<DateTime> start_date
            , Nullable<DateTime> end_date
            , Nullable<int> cmd_dtls_seq
            , string cmd_ref_no
            , Nullable<DateTime> cmd_date
            , string cmd_name

            , string old_pos_ref
            , string old_pos_name
            , string old_class_name
            , string old_job_site_name
            , string old_working_job_site_name
            , string old_dep_name
            , string old_mis_name
            , string old_field_name
            , string old_exec_name
            , Nullable<double> old_salary

            , string new_pos_ref
            , string new_pos_name
            , string new_class_name
            , string new_job_site_name
            , string new_working_job_site_name
            , string new_dep_name
            , string new_mis_name
            , string new_field_name
            , string new_exec_name
            , Nullable<double> new_salary

            , Nullable<int> cmd_type_seq
            , string remarks
            , int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(33);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@emp_seq", "");
                dbManager.AddParameters(2, "@start_date", start_date);
                dbManager.AddParameters(3, "@end_date", end_date);
                dbManager.AddParameters(4, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(5, "@cmd_ref_no", cmd_ref_no);
                dbManager.AddParameters(6, "@cmd_name", cmd_name);
                dbManager.AddParameters(7, "@cmd_date", cmd_date);

                dbManager.AddParameters(8, "@old_pos_ref", old_pos_ref);
                dbManager.AddParameters(9, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(10, "@old_class_name", old_class_name);
                dbManager.AddParameters(11, "@old_job_site_name", old_job_site_name);
                dbManager.AddParameters(12, "@old_working_job_site_name", old_working_job_site_name);
                dbManager.AddParameters(13, "@old_dep_name", old_dep_name);
                dbManager.AddParameters(14, "@old_mis_name", old_mis_name);
                dbManager.AddParameters(15, "@old_field_name", old_field_name);
                dbManager.AddParameters(16, "@old_exec_name", old_exec_name);
                dbManager.AddParameters(17, "@old_salary", old_salary);

                dbManager.AddParameters(18, "@new_pos_ref", new_pos_ref);
                dbManager.AddParameters(19, "@new_pos_name", new_pos_name);
                dbManager.AddParameters(20, "@new_class_name", new_class_name);
                dbManager.AddParameters(21, "@new_job_site_name", new_job_site_name);
                dbManager.AddParameters(22, "@new_working_job_site_name", new_working_job_site_name);
                dbManager.AddParameters(23, "@new_dep_name", new_dep_name);
                dbManager.AddParameters(24, "@new_mis_name", new_mis_name);
                dbManager.AddParameters(25, "@new_field_name", new_field_name);
                dbManager.AddParameters(26, "@new_exec_name", new_exec_name);
                dbManager.AddParameters(27, "@new_salary", new_salary);

                dbManager.AddParameters(28, "@cmd_type_seq", cmd_type_seq);
                dbManager.AddParameters(29, "@remarks", remarks);
                dbManager.AddParameters(30, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(31, "@updated_by", UpdatedBy);
                dbManager.AddParameters(32, "@updated_when", DateTime.Today);

                strSQL = "UPDATE HRM_PS_EMP_POSITION_HISTORIES"
                    + " SET START_DATE = @start_date"
                        + ", END_DATE = @end_date"
                        + ", CMD_DTLS_SEQ = @cmd_dtls_seq"
                        + ", CMD_REF_NO = @cmd_ref_no"
                        + ", CMD_NAME = @cmd_name"
                        + ", CMD_DATE = @cmd_date"
                        + ", CMD_REF_NO = @cmd_ref_no"
                        + ", CMD_NAME = @cmd_name"

                        + ", old_pos_ref = @old_pos_ref"
                        + ", old_pos_name = @old_pos_name"
                        + ", old_class_name = @old_class_name"
                        + ", old_job_site_name = @old_job_site_name"
                        + ", old_working_job_site_name = @old_working_job_site_name"
                        + ", old_dep_name = @old_dep_name"
                        + ", old_mis_name = @old_mis_name"
                        + ", old_field_name = @old_field_name"
                        + ", old_exec_name = @old_exec_name"
                        + ", old_salary = @old_salary"

                        + ", new_pos_ref = @new_pos_ref"
                        + ", new_pos_name = @new_pos_name"
                        + ", new_class_name = @new_class_name"
                        + ", new_job_site_name = @new_job_site_name"
                        + ", new_working_job_site_name = @new_working_job_site_name"
                        + ", new_dep_name = @new_dep_name"
                        + ", new_mis_name = @new_mis_name"
                        + ", new_field_name = @new_field_name"
                        + ", new_exec_name = @new_exec_name"
                        + ", new_salary = @new_salary"

                        + ", cmd_type_seq = @cmd_type_seq"
                        + ", REMARKS = @remarks"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE POS_HIS_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_EMP_POSITION_HISTORIES"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE POS_HIS_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}
